<?php
	require_once("base_mod.php");
	require_once("settings_mod.php");
	
	updateAlerts();
	
	function updateAlerts(){
		// First, drop the current alerts:
		dropAlerts();
		getCombatAlerts();
		getHackAlerts();
		getSightingAlerts();
		getManualAlerts();
		setSupercededAlerts();
	}
	
	function setSupercededAlerts(){
		$conn = AlertBaseMod::getConnection();
		$sql = "select * from alerts a order by a.when asc";
		$rows = mysql_query($sql, $conn);
		while ($row = mysql_fetch_assoc($rows)) {
			$sql = "select * from alerts a where a.attacker = '".$row["attacker"]."' and a.when > '".$row["when"]."' and id <> ".$row["id"];
			echo $sql;
			$superceded = mysql_query($sql, $conn);
			if (mysql_num_rows($superceded ) != 0){
				$sql = "update alerts set superceded = 1 where id = ".$row["id"];
				$foo = mysql_query($sql, $conn); 
			}
		}
	}
	
	function dropAlerts(){
		$conn = AlertBaseMod::getConnection();
		$sql = "DELETE FROM alerts where alert_type = 'CMBT' and timediff(UTC_TIMESTAMP(), alerts.when) > cast('".AlertSettingsMod::COMBAT_ALERT_TIMEFRAME."' as time)";
		$rows = mysql_query($sql, $conn); 
		$sql = "DELETE FROM alerts where alert_type = 'HACK' and timediff(UTC_TIMESTAMP(), alerts.when) > cast('".AlertSettingsMod::HACK_ALERT_TIMEFRAME."' as time)";
		$rows = mysql_query($sql, $conn); 
		$sql = "DELETE FROM alerts where alert_type = 'SGHT' and timediff(UTC_TIMESTAMP(), alerts.when) > cast('".AlertSettingsMod::SIGHTING_ALERT_TIMEFRAME."' as time)";
		$rows = mysql_query($sql, $conn); 
		$sql = "DELETE FROM alerts where alert_type = 'MANL' and timediff(UTC_TIMESTAMP(), alerts.when) > cast('".AlertSettingsMod::MANUAL_ALERT_TIMEFRAME."' as time)";
		$rows = mysql_query($sql, $conn); 
	}
	
	function pilotIsWatched($pilot){
		$conn = AlertBaseMod::getConnection();
		$sql = "select pilot from watched_pilots where pilot = '".$pilot."'";
		$existing = mysql_query($sql, $conn); 
		if(mysql_num_rows($existing) != 0)
			return true;
		else
			return false;
	}
	
	function getCombatAlerts() {
		$conn = AlertBaseMod::getConnection();
		$sql = "SELECT c.id, c.pid, c.when, c.type, c.sector, c.coords, c.attacker, c.defender, c.outcome, c.additional, cl.abbreviation as cluster FROM combat AS c
			left join sector AS s on c.sector=s.sector
			left join cluster as cl on cl.id = s.cluster
			where timediff(UTC_TIMESTAMP(), c.when) < cast('".AlertSettingsMod::COMBAT_ALERT_TIMEFRAME.
			"' as time) and (c.type = 'Ship vs Ship' or c.type = 'Ship vs Building')
			order by c.when desc";
		//echo $sql;
		$rows = mysql_query($sql, $conn); 
		while ($row = mysql_fetch_assoc($rows)) {
			if(AlertSettingsMod::WATCHLIST_ONLY_ALERTS == "yes"){
				if(!pilotIsWatched($row["attacker"]) && !pilotIsWatched($row["defender"]))
					continue;
			}
			$sql = "select pid from alerts where pid = '".$row["id"]."' and alert_type = 'CMBT'";
			$existing = mysql_query($sql, $conn); 
			if (mysql_num_rows($existing) == 0){
				$clustr = $row["cluster"];
				$rsn = $row["additional"];
				if ($rsn != "Ambushed") $rsn = "PvP";
				if ($row["type"] == "Ship vs Building") $rsn = "Building attack";
				if (is_null($clustr)) $clustr = "";
				$sql = sprintf("insert into alerts (".
				       "pid, alert_type, `when`, `type`, cluster, sector, coords, attacker,". 
				       "defender, outcome, additional, detail) values (".
				       "'%d', '%s','%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'".
				       ")",
				       $row["id"],
				       "CMBT",
				       mysql_real_escape_string($row["when"]),
				       mysql_real_escape_string($rsn),
				       mysql_real_escape_string($clustr),
				       mysql_real_escape_string($row["sector"]),
				       mysql_real_escape_string("[".$row["coords"]."]"),
				       mysql_real_escape_string($row["attacker"]),
				       mysql_real_escape_string($row["defender"]),
				       mysql_real_escape_string($row["outcome"]),
				       mysql_real_escape_string($row["additional"]),
				       mysql_real_escape_string("")
				       );
				$foo = mysql_query($sql, $conn); 
			}
		}
	}
	
	function getHackAlerts(){
		$conn = AlertBaseMod::getConnection();
		$sql = "SELECT h.id, h.date, h.pilot, h.method, h.sector, h.coords, cl.abbreviation as cluster 
			FROM hack AS h
			left join sector AS s on h.sector=s.sector
			left join cluster as cl on cl.cluster = h.cluster
			where timediff(UTC_TIMESTAMP(), h.date) < cast('".AlertSettingsMod::HACK_ALERT_TIMEFRAME.
			"' as time) order by h.date desc";
		$rows = mysql_query($sql, $conn); 
		while ($row = mysql_fetch_assoc($rows)) {
			if(AlertSettingsMod::WATCHLIST_ONLY_ALERTS == "yes"){
				if(!pilotIsWatched($row["pilot"]))
					continue;
			}
			$sql = "select pid from alerts where pid = '".$row["id"]."' and alert_type = 'HACK'";
			$existing = mysql_query($sql, $conn); 
			if (mysql_num_rows($existing) == 0){
				$clustr = $row["cluster"];
				$rsn = $row["additional"];
				if ($rsn != "Ambushed") $rsn = "PvP";
				if ($row["type"] == "Ship vs Building") $rsn = "Building attack";
				if (is_null($clustr)) $clustr = "";
				$sql = sprintf("insert into alerts (".
				       "pid, alert_type, `when`, `type`, cluster, sector, coords, attacker,". 
				       "defender, outcome, additional, detail) values (".
				       "'%d', '%s','%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'".
				       ")",
				       $row["id"],
				       "HACK",
				       mysql_real_escape_string($row["date"]),
				       mysql_real_escape_string($row["method"]." hack"),
				       mysql_real_escape_string($clustr),
				       mysql_real_escape_string($row["sector"]),
				       mysql_real_escape_string($row["coords"]),
				       mysql_real_escape_string($row["pilot"]),
				       mysql_real_escape_string(""),
				       mysql_real_escape_string(""),
				       mysql_real_escape_string(""),
				       mysql_real_escape_string("")
				       );
				$foo = mysql_query($sql, $conn); 
			}
		}
	}
	
	function getSightingAlerts(){
		$conn = AlertBaseMod::getConnection();
		$sql = "SELECT si.id, si.date, si.pilot_name, si.sector, si.coords, cl.abbreviation as cluster 
			FROM sighting AS si
			left join sector AS s on si.sector=s.sector
			left join cluster as cl on cl.id = s.cluster
			where timediff(UTC_TIMESTAMP(), si.date) < cast('".AlertSettingsMod::SIGHTING_ALERT_TIMEFRAME.
			"' as time) order by si.date desc";
		$rows = mysql_query($sql, $conn); 
		while ($row = mysql_fetch_assoc($rows)) {
			if(AlertSettingsMod::WATCHLIST_ONLY_ALERTS == "yes"){
				if(!pilotIsWatched($row["pilot_name"]))
					continue;
			}
			$sql = "select pid from alerts where pid = '".$row["id"]."' and alert_type = 'SGHT'";
			$existing = mysql_query($sql, $conn); 
			if (mysql_num_rows($existing) == 0){
				$clustr = $row["cluster"];
				if (is_null($clustr)) $clustr = "";
				$sql = sprintf("insert into alerts (".
				       "pid, alert_type, `when`, `type`, cluster, sector, coords, attacker,". 
				       "defender, outcome, additional, detail) values (".
				       "'%d', '%s','%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'".
				       ")",
				       $row["id"],
				       "SGHT",
				       mysql_real_escape_string($row["date"]),
				       mysql_real_escape_string(" sighted"),
				       mysql_real_escape_string($clustr),
				       mysql_real_escape_string($row["sector"]),
				       mysql_real_escape_string($row["coords"]),
				       mysql_real_escape_string($row["pilot_name"]),
				       mysql_real_escape_string(""),
				       mysql_real_escape_string(""),
				       mysql_real_escape_string(""),
				       mysql_real_escape_string("")
				       );
				$foo = mysql_query($sql, $conn); 
			}
		}
	}
	
	function getManualAlerts(){
		$conn = AlertBaseMod::getConnection();
		$sql = "SELECT m.id, m.date, m.header, m.sector, m.coords, cl.abbreviation as cluster 
			FROM manual_alert AS m
			left join sector AS s on m.sector=s.sector
			left join cluster as cl on cl.id = s.cluster
			where timediff(UTC_TIMESTAMP(), m.date) < cast('".AlertSettingsMod::MANUAL_ALERT_TIMEFRAME.
			"' as time) order by m.date desc";
		$rows = mysql_query($sql, $conn); 
		while ($row = mysql_fetch_assoc($rows)) {
			if(AlertSettingsMod::WATCHLIST_ONLY_ALERTS == "yes"){
				if(!pilotIsWatched($row["header"]))
					continue;
			}
			$sql = "select pid from alerts where pid = '".$row["id"]."' and alert_type = 'MANL'";
			$existing = mysql_query($sql, $conn); 
			if (mysql_num_rows($existing) == 0){
				$clustr = $row["cluster"];
				if (is_null($clustr)) $clustr = "";
				$sql = sprintf("insert into alerts (".
				       "pid, alert_type, `when`, `type`, cluster, sector, coords, attacker,". 
				       "defender, outcome, additional, detail) values (".
				       "'%d', '%s','%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'".
				       ")",
				       $row["id"],
				       "MANL",
				       mysql_real_escape_string($row["date"]),
				       mysql_real_escape_string(" manual alert"),
				       mysql_real_escape_string($clustr),
				       mysql_real_escape_string($row["sector"]),
				       mysql_real_escape_string($row["coords"]),
				       mysql_real_escape_string($row["header"]),
				       mysql_real_escape_string(""),
				       mysql_real_escape_string(""),
				       mysql_real_escape_string(""),
				       mysql_real_escape_string("")
				       );
				$foo = mysql_query($sql, $conn); 
			}
		}
	}
?>